package com.echo.dao;

import com.echo.entity.User;
import com.echo.util.JdbcUtils;
import com.echo.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class UserDao {

    private static final Logger logger = Logger.getLogger(UserDao.class.getName());
    
    // 1. 注册：插入新用户
    public void insert(User user) throws SQLException {
        logger.info("===== 开始执行用户数据插入 ====");
        logger.info("要插入的用户信息: " + user.getUsername() + ", " + user.getEmail());
        
        String sql = "INSERT INTO users(username, password, email, nickname, create_time, status, role) " +
                "VALUES(?, ?, ?, ?, NOW(), 1, 0)"; // 默认为正常状态、普通用户
        logger.info("SQL语句: " + sql);
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            logger.info("尝试获取数据库连接");
            conn = JdbcUtils.getConnection();
            logger.info("数据库连接获取成功: " + conn);
            
            // 显式设置为手动提交事务
            conn.setAutoCommit(false);
            logger.info("已设置连接为手动提交事务");
            
            logger.info("准备创建PreparedStatement");
            pstmt = conn.prepareStatement(sql);
            
            // 设置参数并记录
            logger.info("设置SQL参数 - 用户名: " + user.getUsername());
            pstmt.setString(1, user.getUsername());
            
            logger.info("设置SQL参数 - 密码: [加密后]");
            pstmt.setString(2, user.getPassword()); // 传入加密后的密码
            
            logger.info("设置SQL参数 - 邮箱: " + user.getEmail());
            pstmt.setString(3, user.getEmail());
            
            String finalNickname = StringUtils.isNotEmpty(user.getNickname()) ? user.getNickname() : user.getUsername();
            logger.info("设置SQL参数 - 昵称: " + finalNickname);
            pstmt.setString(4, finalNickname);
            
            logger.info("执行SQL插入操作");
            int affectedRows = pstmt.executeUpdate();
            logger.info("SQL执行完成，影响行数: " + affectedRows);
            
            // 显式提交事务
            conn.commit();
            logger.info("事务已成功提交");
            
            if (affectedRows <= 0) {
                logger.warning("警告：插入操作没有影响任何行");
            }
            
        } catch (SQLException e) {
            // 发生异常时回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                    logger.info("事务已回滚");
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "事务回滚失败", ex);
                }
            }
            logger.log(Level.SEVERE, "数据库插入异常: " + e.getMessage(), e);
            logger.log(Level.SEVERE, "SQLState: " + e.getSQLState() + ", ErrorCode: " + e.getErrorCode());
            throw e;
        } finally {
            // 恢复连接的自动提交状态
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    logger.info("已恢复连接的自动提交状态");
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, "恢复自动提交状态失败", ex);
                }
            }
            logger.info("开始清理数据库资源");
            JdbcUtils.close(conn, pstmt);
            logger.info("数据库资源清理完成");
        }
        
        logger.info("===== 用户数据插入操作完成 ====");
    }

    // 2. 校验用户名是否已存在
    public boolean existsUsername(String username) throws SQLException {
        String sql = "SELECT id FROM users WHERE username = ? LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            return rs.next(); // 有结果则存在
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
    }

    // 3. 校验邮箱是否已存在
    public boolean existsEmail(String email) throws SQLException {
        String sql = "SELECT id FROM users WHERE email = ? LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, email);
            rs = pstmt.executeQuery();
            return rs.next();
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
    }

    // 4. 登录：根据用户名查询完整用户信息
    public User findByUsername(String username) throws SQLException {
        String sql = "SELECT id, username, password, email, nickname, avatar, status, role " +
                "FROM users WHERE username = ? LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setNickname(rs.getString("nickname"));
                user.setAvatar(rs.getString("avatar"));
                user.setStatus(rs.getInt("status"));
                user.setRole(rs.getInt("role"));
                return user;
            }
            return null; // 无此用户
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
    }

    // 5. 个人资料：根据ID查询用户信息
    public User findById(Integer id) throws SQLException {
        String sql = "SELECT username, nickname, email, avatar, create_time, status, password, role FROM users WHERE id = ? LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(id);
                user.setUsername(rs.getString("username"));
                user.setNickname(rs.getString("nickname"));
                user.setEmail(rs.getString("email"));
                user.setAvatar(rs.getString("avatar"));
                // 先获取Timestamp再转换为java.util.Date，以保留完整时间信息
                java.sql.Timestamp timestamp = rs.getTimestamp("create_time");
                if (timestamp != null) {
                    user.setCreateTime(new java.util.Date(timestamp.getTime()));
                }
                user.setStatus(rs.getInt("status"));
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getInt("role"));
                return user;
            }
            return null;
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
    }

    // 6. 个人资料：更新昵称、邮箱、头像
    public void updateProfile(User user) throws SQLException {
        String sql = "UPDATE users SET nickname = ?, email = ?, avatar = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getNickname());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getAvatar()); // 可为null（不修改头像）
            pstmt.setInt(4, user.getId());
            pstmt.executeUpdate();
        } finally {
            JdbcUtils.close(conn, pstmt);
        }
    }

    // 7. 校验邮箱唯一性（排除当前用户）
    public boolean existsEmailExceptId(String email, Integer id) throws SQLException {
        String sql = "SELECT id FROM users WHERE email = ? AND id != ? LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, email);
            pstmt.setInt(2, id);
            rs = pstmt.executeQuery();
            return rs.next(); // 存在则返回true（邮箱已被占用）
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
    }

    // 8. 更新密码（使用外部连接，用于事务控制）
    public void updatePassword(Integer userId, String newPassword, Connection conn) throws SQLException {
        logger.info("===== 开始执行密码更新操作 ====");
        logger.info("用户ID: " + userId + ", 新密码: [加密后]");
        
        String sql = "UPDATE users SET password = ? WHERE id = ?";
        logger.info("SQL语句: " + sql);
        
        PreparedStatement pstmt = null;
        try {
            logger.info("准备创建PreparedStatement");
            pstmt = conn.prepareStatement(sql);
            
            logger.info("设置SQL参数 - 密码: [加密后]");
            pstmt.setString(1, newPassword);
            
            logger.info("设置SQL参数 - 用户ID: " + userId);
            pstmt.setInt(2, userId);
            
            logger.info("执行SQL更新操作");
            int affectedRows = pstmt.executeUpdate();
            logger.info("SQL执行完成，影响行数: " + affectedRows);
            
            if (affectedRows <= 0) {
                logger.warning("警告：密码更新操作没有影响任何行");
                throw new SQLException("密码更新失败，未找到用户ID: " + userId);
            }
            
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库更新异常: " + e.getMessage(), e);
            logger.log(Level.SEVERE, "SQLState: " + e.getSQLState() + ", ErrorCode: " + e.getErrorCode());
            throw e;
        } finally {
            logger.info("开始清理数据库资源");
            if (pstmt != null) {
                try {
                    pstmt.close();
                    logger.info("PreparedStatement已关闭");
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭PreparedStatement失败", e);
                }
            }
            logger.info("数据库资源清理完成");
        }
        
        logger.info("===== 密码更新操作完成 ====");
    }
    
    // 9. 更新密码（标准版本，自动管理连接）
    public void updatePassword(Integer userId, String newPassword) throws SQLException {
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            updatePassword(userId, newPassword, conn);
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "事务回滚失败", ex);
                }
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, "恢复自动提交状态失败", ex);
                }
                JdbcUtils.closeConnection(conn);
            }
        }
    }
    
    // 带Connection参数的方法重载版本，用于事务管理
    public void insert(User user, Connection conn) throws SQLException {
        logger.info("===== 开始执行用户数据插入(外部连接) ====");
        logger.info("要插入的用户信息: " + user.getUsername() + ", " + user.getEmail());
        
        String sql = "INSERT INTO users(username, password, email, nickname, create_time, status, role) " +
                "VALUES(?, ?, ?, ?, NOW(), 1, 0)";
        logger.info("SQL语句: " + sql);
        
        PreparedStatement pstmt = null;
        try {
            logger.info("准备创建PreparedStatement");
            pstmt = conn.prepareStatement(sql);
            
            logger.info("设置SQL参数 - 用户名: " + user.getUsername());
            pstmt.setString(1, user.getUsername());
            
            logger.info("设置SQL参数 - 密码: [加密后]");
            pstmt.setString(2, user.getPassword());
            
            logger.info("设置SQL参数 - 邮箱: " + user.getEmail());
            pstmt.setString(3, user.getEmail());
            
            String finalNickname = StringUtils.isNotEmpty(user.getNickname()) ? user.getNickname() : user.getUsername();
            logger.info("设置SQL参数 - 昵称: " + finalNickname);
            pstmt.setString(4, finalNickname);
            
            logger.info("执行SQL插入操作");
            int affectedRows = pstmt.executeUpdate();
            logger.info("SQL执行完成，影响行数: " + affectedRows);
            
            if (affectedRows <= 0) {
                logger.warning("警告：插入操作没有影响任何行");
                throw new SQLException("用户注册失败");
            }
            
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库插入异常: " + e.getMessage(), e);
            logger.log(Level.SEVERE, "SQLState: " + e.getSQLState() + ", ErrorCode: " + e.getErrorCode());
            throw e;
        } finally {
            logger.info("开始清理数据库资源");
            if (pstmt != null) {
                try {
                    pstmt.close();
                    logger.info("PreparedStatement已关闭");
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭PreparedStatement失败", e);
                }
            }
            logger.info("数据库资源清理完成");
        }
        
        logger.info("===== 用户数据插入操作完成 ====");
    }
    
    // 带Connection参数的方法重载版本，用于事务管理
    public User findByUsername(String username, Connection conn) throws SQLException {
        String sql = "SELECT id, username, password, email, nickname, avatar, status, role " +
                "FROM users WHERE username = ? LIMIT 1";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setNickname(rs.getString("nickname"));
                user.setAvatar(rs.getString("avatar"));
                user.setStatus(rs.getInt("status"));
                user.setRole(rs.getInt("role"));
                return user;
            }
            return null; // 无此用户
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭ResultSet失败", e);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭PreparedStatement失败", e);
                }
            }
        }
    }
    
    // 10. 删除用户（带Connection参数，用于事务管理）
    public void deleteById(Integer userId, Connection conn) throws SQLException {
        logger.info("===== 开始执行用户删除操作(外部连接) ====");
        logger.info("用户ID: " + userId);
        
        String sql = "DELETE FROM users WHERE id = ?";
        logger.info("SQL语句: " + sql);
        
        PreparedStatement pstmt = null;
        try {
            logger.info("准备创建PreparedStatement");
            pstmt = conn.prepareStatement(sql);
            
            logger.info("设置SQL参数 - 用户ID: " + userId);
            pstmt.setInt(1, userId);
            
            logger.info("执行SQL删除操作");
            int affectedRows = pstmt.executeUpdate();
            logger.info("SQL执行完成，影响行数: " + affectedRows);
            
            if (affectedRows <= 0) {
                logger.warning("警告：删除操作没有影响任何行");
                throw new SQLException("用户删除失败，未找到用户ID: " + userId);
            }
            
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "数据库删除异常: " + e.getMessage(), e);
            logger.log(Level.SEVERE, "SQLState: " + e.getSQLState() + ", ErrorCode: " + e.getErrorCode());
            throw e;
        } finally {
            logger.info("开始清理数据库资源");
            if (pstmt != null) {
                try {
                    pstmt.close();
                    logger.info("PreparedStatement已关闭");
                } catch (SQLException e) {
                    logger.log(Level.WARNING, "关闭PreparedStatement失败", e);
                }
            }
            logger.info("数据库资源清理完成");
        }
        
        logger.info("===== 用户删除操作完成 ====");
    }
    
    // 11. 删除用户（标准版本，自动管理连接）
    public void deleteById(Integer userId) throws SQLException {
        Connection conn = null;
        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);
            deleteById(userId, conn);
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "事务回滚失败", ex);
                }
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, "恢复自动提交状态失败", ex);
                }
                JdbcUtils.closeConnection(conn);
            }
        }
    }
}